<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

/**
 * Journey Cycle atau disingkat JC adalah modul untuk mengatur jadwal:
 * 1. Kunjungan SFA ke Outlet
 * 2. Kunjungan Direct Sales ke Agent dan untuk Mobile Selling
 * 3. Kunjungan SGA ke Outlet, Outlet SGO dan Mobile Selling
 * 4. Kunjungan CC dan SPA (Supervisor Area) ke Outlet, Agent atau lokasi Mobsell
 * 5. Supervisi CC dan SPA (Supervisor Area) terhadap sales team (DS, SFA, SGA)
 * 6. Kunjungan DSTL (DS Team Leader) ke Agent dan lokasi Mobsell
 * 7. Supervisi DSTL kepada anggota Direct Sales
 *
 * Rule yang berlaku pada Journey Cycle antara lain:
 * 1. Agent Sell In
 * 2. Outlet Sell In
 * 3. Outlet Sell Out
 * 4. Mobile Selling
 *
 * ATTENTION..!!
 * Untuk menjaga agar modul ini bersih dan human readable, maka susunlah methoc-method yang dibuat berdasarkan pattern yang sudah terbentuk.
 */


class Jc extends CI_Controller
{

	public function __construct() {
		parent::__construct();
		$this->load->library('enc');
		$this->load->model('mservices');
		$this->load->model('mmaster' );
		$this->load->model('mglobal');
		$this->load->model('mjc');
		$this->load->model('mjourney');
		$this->load->model('musers');
		$this->is_logged_in();
	}

	public function index()
	{
		redirect(base_url('index.php/jc/cc'));
	}

	public function is_logged_in()
	{
		$is_logged_in = $this->session->userdata('is_logged_in');
		if(!isset($is_logged_in) || $is_logged_in != true)
		{
			redirect(base_url() .'index.php/login');
		}
	}


	/**
	 * Digunakan untuk menampilkan data-data outlet ataupun agent, ketika tombol tampilkan JC diinvoke.
	 * @return json dalam format table yang kemudian di parsing menggunakan JSON.Parse(data)
	 */
	public function load_journey() {
		/*
		* Paremeters and variables are initiated here
		* Primary variables must have a value, while secondaries aren't
		*/
		## primary parameters
		$month      	 = $this->input->post("month_work");
		$sales_id   	 = $this->input->post("sales_id");
		$user_group_id = $this->input->post("user_group_id");
		$agent_type    = $this->input->post("agent_type"); // for Direct Sales (Agent) Only

		## secondary parameters
		$programs     = $this->input->post("program");
		$keyword      = trim( htmlspecialchars( strtoupper( $this->input->post("keyword") ) ) ); // Search parameters
		$selling_type = $this->input->post("selling_type");
		$load_more    = $this->input->post("_s1"); // define if the "load more data" is requested
		if ($load_more) {
			$limit      = 10;
			$offset     = $this->input->post("_s2");
		}
		else {
			$limit = 15; $offset = 0; 
		}

		/*
		* WARNING !!
		* Urutan variabel untuk memanggil $journey sudah diatur sedemikian rupa, JANGAN DIUBAH YA BRO !!!
		* 1.  cluster_id
		* 2.  user_group_id
		* 3.  sales_id
		* 4.  group_id
		* 5.  month
		* 6.  keyword (maybe null)
		* 7.  limit (default 15 dan 10 untuk load_more = true)
		* 8.  offset (default 0)
		* 9.  agent_type (agent/mobsell)
		* 10. selling_type (umum/edukasi/perkantoran)
		* 11. program array() ---------------------------------- 19/02/2013
		*/
	
		$cluster_id = $this->mglobal->user_cluster($sales_id);
		$holidays   = $this->mjc->get_holidays($month);

		$channels_maintained = $this->mjc->channels_maintained($user_group_id, $sales_id, $agent_type, $selling_type);
		$daily_counts        = $this->mjc->daily_counts($user_group_id, $sales_id, $month); // ambil jumlah kunjungan per kolom dalam satu bulan
		$journey             = $this->mjc->jc_sales($cluster_id, $user_group_id, $sales_id, $month, $keyword, $limit, $offset, $agent_type, $selling_type, $programs);
		// echo $this->db->last_query();
		// die();
		if ($load_more OR $keyword) {
			$journey_table = $this->load->view('jc/jc_append_'.$user_group_id, array('journey' =>$journey, 'channels_maintained' => $channels_maintained, 'calendar' => $month, 'holidays' => $holidays, 'sales_id' => $sales_id, 'offset' => $offset), TRUE);
			echo json_encode(array(
				'_count'   => count($journey),
				'_summary' => $journey_table,
			));
		}
		else {
			$journey_table = $this->load->view('jc/jc_table_'.$user_group_id, array('journey' =>$journey, 'channels_maintained' => $channels_maintained, 'calendar' => $month, 'holidays' => $holidays, 'sales_id' => $sales_id, 'daily_counts' => $daily_counts ), TRUE); 
			echo json_encode(array(
				'journey_status'      => TRUE,
				'channels_maintained' => $channels_maintained,
				'journey_summary'     => $journey_table
			));
		}
		return; 
	}

	public function daily_counts() {
		
	}

	public function ds($sales_id="", $week="") {
		$data['template']  = 'shell/smooth';
		$data['main_view'] = 'jc/ds';
		$data['title']     = 'Journe Cycle :: Direct Sales';

    $cluster_id                 = $this->mglobal->user_cluster($this->session->userdata("username"));
		$data["agent"]              = $this->mglobal->get_sales($cluster_id, array(9) );
		$data['agent_status']       = $this->mservices->getValueFromTable("v_combo_agent_status");
		$data['agent_selling_type'] = $this->mservices->getValueFromTable("v_combo_agent_selling_type");
		$data['months']             = $this->_calendar();

		$teritori = explode('>', $this->mglobal->getTerritoryPath());
		$data['region']  = $teritori[0];
		$data['cluster'] = $teritori[1];

		if ($teritori[1]) {
			$this->load->view($data['template'], $data, FALSE);
		}
		else
		{
			redirect("index.php/cluster");
		}
	}

	public function sfa($sales_id="", $week="") {
		$data['template']  = 'shell/smooth';
		$data['main_view'] = 'jc/sfa';
		$data['title']     = 'Journe Cycle :: Smartfren Ambassador';
		$cluster_id        = $this->mglobal->user_cluster($this->session->userdata("username"));
		$data['months']    = $this->_calendar();
		if($this->session->userdata('is_self_service') == 1) 
			{ redirect(base_url().'index.php/jc/smile'); }
		else
			{ $data["sfa"] = $this->mglobal->get_sales($cluster_id, array(8) ); }

		$teritori = explode('>', $this->mglobal->getTerritoryPath());
		$data['region']  = $teritori[0];
		$data['cluster'] = $teritori[1];

		if ($teritori[1]) 
			{ $this->load->view($data['template'], $data, FALSE); }
		else {
			$this->session->set_flashdata('messege', 'Silakan pilih cluster terlebih dahulu');
			redirect("index.php/cluster");
		}
	}

	public function sgs() {
		$data['template']  = 'shell/smooth';
		$data['main_view'] = 'jc/sgs';
		$data['title']     = 'Journe Cycle :: Smartfren Gadget Specialist';

		$cluster_id        = $this->mglobal->user_cluster($this->session->userdata("username"));
		$data["user"]      = $this->mglobal->get_sales($cluster_id, array(31) );
		$data['months']    = $this->_calendar();

		$teritori          = explode('>', $this->mglobal->getTerritoryPath());
		$data['region']    = $teritori[0];
		$data['cluster']   = $teritori[1];

		if ($teritori[1]) {
			$this->load->view($data['template'], $data, FALSE);
		}
		else {
			$this->session->set_flashdata('messege', 'Silakan pilih cluster terlebih dahulu');
			redirect("index.php/cluster");
		}
	}

	public function sga() {
		$data['template']  = 'shell/smooth';
		$data['main_view'] = 'jc/sga';
		$data['title']     = 'Journe Cycle :: Smartfren Gadget Ambassador';

		$cluster_id        = $this->mglobal->user_cluster($this->session->userdata("username"));
		$data["sga"]       = $this->mglobal->get_sales($cluster_id, array(35) );
		$data['agent_status'] = $this->mservices->getValueFromTable("v_combo_agent_status");
		$data['months']    = $this->_calendar();

		$teritori          = explode('>', $this->mglobal->getTerritoryPath());
		$data['region']    = $teritori[0];
		$data['cluster']   = $teritori[1];

		if ($teritori[1]) {
			$this->load->view($data['template'], $data, FALSE);
		}
		else {
			$this->session->set_flashdata('messege', 'Silakan pilih cluster terlebih dahulu');
			redirect("index.php/cluster");
		}
	}

	public function smile($sales_id="", $week="") {
		$data['template']  = 'shell/smooth';
		$data['main_view'] = 'jc/sml';
		$data['title']     = 'Journe Cycle :: Smiley';

		// $cluster_id = $this->mjc->get_cluster_id($this->session->userdata('username'));
		// $data['sfa'] = $this->mjc->get_sales_team(8, $cluster_id); // 8 is user group id for Smartfren Ambassador
    $cluster_id = $this->mglobal->user_cluster($this->session->userdata("username"));
	  $data["sfa"] = $this->mglobal->get_sales($cluster_id, array(32) );
		$data['months'] = $this->_calendar();

		$teritori = explode('>', $this->mglobal->getTerritoryPath());
		$data['region'] = $teritori[0];
		$data['cluster'] = $teritori[1];

		if ($teritori[1]) {
			$this->load->view($data['template'], $data, FALSE);
		}
		else {
			$this->session->set_flashdata('messege', 'Silakan pilih cluster terlebih dahulu');
			redirect("index.php/cluster");
		}
	}

	public function sgp()
	{
		$data['template']  = 'shell/smooth';
		$data['main_view'] = 'jc/sgp';
		$data['title']     = 'Journe Cycle :: Smartfren Gadget Promotor';

    $cluster_id        = $this->mglobal->user_cluster($this->session->userdata("username"));
	  $data["sgp"]       = $this->mglobal->get_sales($cluster_id, array(36) );
		$data['months']    = $this->_calendar();
	  $data["category"]  = $this->mjc->channel_categories($cluster_id);

		$teritori = explode('>', $this->mglobal->getTerritoryPath());
		$data['region'] = $teritori[0];
		$data['cluster'] = $teritori[1];

		if ($teritori[1]) {
			$this->load->view($data['template'], $data, FALSE);
		}
		else {
			$this->session->set_flashdata('messege', 'Silakan pilih cluster terlebih dahulu');
			redirect("index.php/cluster");
		}
	}

	private function _calendar() {

		// Depracated
		// for ($i=0; $i < 6; $i++) { 
		// 	$work_days[] = array(date('Ym', strtotime("-".($i-2)." month")), date('F Y', strtotime("-".($i-2)." month")) );
		// }

		// rizki@nutech-integrasi.com -- 01/29/2013
		$month = array(
			'01' => 'Januari', '02' => 'Februari', '03' => 'Maret', '04' => 'April', '05' => 'Mei', '06' => 'Juni',
			'07' => 'Juli', '08' => 'Agustus', '09' => 'September', '10' => 'Oktober', '11' => 'November', '12' => 'Desember'
		);
		$current_month = date('m');
		for ($i=3; $i > -3; $i--) { 
			if($current_month+$i>=1 && $current_month+$i<=12 ) {
				$m=$current_month+$i;
				if($m<=9)
					{ $m='0'.$m; }
				$y=date('Y');
				$work_days[] = array($y.$current_month+$i,$month[$m].' '.$y);
			} elseif($current_month+$i<1) {
				$m=$current_month+$i+12;
				if($m<=9)
					{ $m='0'.$m; }
				$y=date('Y')-1;
				$work_days[] = array($y.$m,$month[$m].' '.$y);
			} else {
				$m=$current_month+$i-12;
				if($m<=9)
					{ $m='0'.$m; }
				$y=date('Y')+1;
				$work_days[]=array($y.$m,$month[$m].' '.$y);
			}
		}
		return $work_days;
	}

	// $sales_type = user_group_id
	function save_jc($sales_type) {
		$data['user_id']      = $this->input->post("user_id");
		$data['channel_id']   = $this->input->post("channel_id");
		$data['territory_id'] = $this->input->post("territory_id");
		$data['yyyymm']       = $this->input->post("month");
		$data['day']          = $this->input->post("day");
		$data['value']        = $this->input->post("nilai");
		$data['jc_type']      = $this->input->post('jc_type');
		if($this->input->post('sales_id'))
			{ $data['sales_id']     = $this->input->post('sales_id'); }
		$save = $this->mjc->save_jc($sales_type, $data);
		if ($save["messege"]) 
			{ echo json_encode( $save ); }
	}

	function save_jc_merchan($sales_type) {
		$data['user_id']      = $this->input->post("user_id");
		$data['channel_id']   = $this->input->post("channel_id");
		$data['territory_id'] = $this->input->post("territory_id");
		$data['yyyymm']       = $this->input->post("month");
		$data['day']          = $this->input->post("day");
		$data['value']        = $this->input->post("nilai");
		$data['jc_type']      = false;
		$data['sales_id']     = $this->input->post('sales_id');

		$save = $this->mjc->save_jc($sales_type, $data);

		if ($save["messege"]) 
			{ echo json_encode( $save ); }
	}

	/*
	**	Display the jorney cycle for Cluster SPV and Direct Sales SPV
	**	Limited to Current day, yesterday and tomorrow Journey Cycle (hardcoded)
	*/
	public function spv()
	{
		$user    = explode(';', $this->input->post('user_id'));
		$journey = $this->mjc->spv_jc($user);
		$journey_table = $this->load->view('jc/jc_'.$user[1] , array('journey' =>$journey), TRUE);
		echo json_encode(array ('jc_summary' => $journey_table));
	}

	public function journey_spv() {
		$uri      = $this->input->post('uri');
		$sales_id = explode(';',$this->input->post("sales_id"));
		// echo 	count($sales_id);
		if( count($sales_id) == '1' and $uri )
			{ $sales_id = array(false, $uri); }
		if( count($sales_id) == '1' and !$uri )
			{ $sales_id = array(false, null); }
		// else
			// { $sales_id = array(false, null); }
		$date     = explode('-',$this->input->post('date'));
		$spv      = explode(';',$this->input->post('spv_id'));
		$keyword  = strtolower($this->input->post('keyword'));
		$jc_type  = $this->input->post('jc_type');
		$as_type  = $this->input->post('mobsel');
		// $limit   = "";  $offset;

		// $keyword    = strtoupper($this->input->post("keyword")); // Search parameters
		$month      = $this->input->post("month_work"); // yyyymm
		$agent_type = $this->input->post("agent_type"); // for Direct Sales (Agent) Only

		## secondary parameters
		// $keyword      = strtoupper($this->input->post("keyword")); // Search parameters
		$load_more    = $this->input->post("_s1"); // define if the "load more data" is requested
		if ($load_more) {
			$limit      = 10;
			$offset     = $this->input->post("_s2");
		}
		else {
			$limit = 15; $offset = 0; 
		}

		$journey = $this->mjc->get_spv_jc($sales_id, $date, $spv, $keyword, $jc_type, $as_type, $limit, $offset);
		if($load_more)
			{ $journey_table = $this->load->view('jc/jc_append_'.$spv[1], array('journey' =>$journey, 'date'=> $date, 'offset' => $offset), TRUE); }
		else 
			{ $journey_table = $this->load->view('jc/jc_table_'.$spv[1], array('journey' =>$journey, 'date'=> $date), TRUE); }

	  if($as_type == "2")
	  	{ $type = 'Mobsell'; }
	  else
	  	{ $type = 'Agent'; }

		$humanized = "Journey Cycle DSTL tanggal ".date("d F Y", mktime(0, 0, 0, $date[1], $date[2], $date[0]))." ";
	  if($sales_id)
	  {
	  	$humanized .= "untuk $jc_type $sales_id ";
	  }

	  $humanized .= "dan tipe agen adalah $type";

		echo json_encode(
				array(
						'journey_summary' => $journey_table,
						'journey_count'   => count($journey) 
					)
			);

	}

	public function cc($action="", $user_group_id="")
	{
		$user_goup  = $this->session->userdata('group');
		$cluster_id = $this->mglobal->user_cluster($this->session->userdata('username'));
		$template  = 'shell/smooth';
		if ($action == 'add' && $user_group_id) {
			if($user_group_id == '8')
				{ 
					$data['main_view'] = 'jc/jc_4_add_8'; 
					$data['sales']     = $this->mglobal->get_sales($cluster_id, array(8, 31) );
				}
			else
				{ 
					$data['main_view'] = 'jc/jc_4_add_9'; 
					$data['sales'] 		 = $this->mglobal->get_sales($cluster_id, array(9) );
				}
			$data['title']     = 'Penambahan Journe Cycle :: Cluster Coordinator (Cluster Manager)';
		}
		else {
			$data['title']     = 'Journe Cycle :: Cluster Coordinator (Cluster Manager)';
			$data['main_view'] = 'jc/jc_4';
		}

		$data['username']  = $this->mglobal->get_sales($cluster_id, array(4));
		
		$teritori = explode('>', $this->mglobal->getTerritoryPath());
		$data['region'] = $teritori[0];
		$data['cluster'] = $teritori[1];

		if ($teritori[1]) {
			$data['journey'] = $this->mjc->spv_jc( array($this->session->userdata('username'),4) );
			
			$this->load->view($template, $data, FALSE);
		}
		else
		{
			redirect("index.php/cluster");
		}
	}

	public function spa($action="", $user_group_id="")
	{
		$user_goup  = $this->session->userdata('group');
		$cluster_id = $this->mglobal->user_cluster($this->session->userdata('username'));
		$template  = 'shell/smooth';
		if ($action == 'add' && $user_group_id) {
			if($user_group_id == '8')
				{ 
					$data['main_view'] = 'jc/jc_34_add_8'; 
					$data['sales'] 		 = $this->mglobal->get_sales($cluster_id, array(8, 31) );
				}
			else
				{ 
					$data['main_view'] = 'jc/jc_34_add_9'; 
					$data['sales'] 		 = $this->mglobal->get_sales($cluster_id, array(9) );
				}
			$data['title']     = 'Penambahan Journe Cycle :: Supervisor Area (Cluster Supervisor)';
		} else {
			$data['title']     = 'Journe Cycle :: Supervisor Area (Cluster Supervisor)';
			$data['main_view'] = 'jc/jc_34';
		}

		$data['username']  = $this->mglobal->get_sales($cluster_id, array(4));
		
		$teritori = explode('>', $this->mglobal->getTerritoryPath());
		$data['region'] = $teritori[0];
		$data['cluster'] = $teritori[1];

		if ($teritori[1]) {
			$data['journey'] = $this->mjc->spv_jc( array($this->session->userdata('username'),34) );
			
			$this->load->view($template, $data, FALSE);
		}
		else
		{
			redirect("index.php/cluster");
		}
	}

	public function dstl($action="")
	{
		$user_goup         = $this->session->userdata('group');
		$template  				 = 'shell/smooth';
		if($action == "add")
			{ $data['main_view'] = 'jc/dstl-add'; }
		else
			{ $data['main_view'] = 'jc/dstl'; }
		$data['title']     = 'Direct Sales Team Leader';

    $cluster_id        = $this->mglobal->user_cluster($this->session->userdata("username"));
		$data["dstl"]      = $this->mglobal->get_sales($cluster_id, array(14) );
		$data["ds"]      	 = $this->mglobal->get_sales($cluster_id, array(9) );

		$teritori        	 = explode('>', $this->mglobal->getTerritoryPath());
		$data['region']  	 = $teritori[0];
		$data['cluster'] 	 = $teritori[1];

		if ($teritori[1]) {
			$this->load->view($template, $data, FALSE);
		}
		else
		{
			$this->session->set_flashdata('message', 'Silakan pilih cluster terlebih dahulu');
			redirect("index.php/cluster");
		}
	}

	// AJAX Operation only
	// Get sales Journey Cycle for sell in, eload and consignment transactions
	public function get_eload_jc()
	{
		$user_id 	  = $this->input->post('user_id');
		$group_id   = $this->input->post('group_id');
		$trans_date = $this->input->post('trans_date');
		$date 			= explode('-', $trans_date);

		$data = $this->mjc->get_eload_jc($group_id, $user_id, $date);
		echo json_encode($data);
	}

	//////////////////////////////////////////
	// JC Merchandiser //////////////////////
	// /Untuk Testing///////////////////////
	///////////////////////////////////////
	function merchan($action="", $user_group_id="")
	{
		$user_goup  = $this->session->userdata('group');
		$cluster_id = $this->mglobal->user_cluster($this->session->userdata('username'));
		$template  = 'shell/smooth';
		if ($action == 'add' ) {
		// 	if($user_group_id == '6')
		// 		{ 
					$data['main_view'] = 'jc/jc_6_add_8'; 
					$data['sales'] 		 = $this->mglobal->get_sales($cluster_id, array(6) );
				// }
		// 	else
		// 		{ 
		// 			$data['main_view'] = 'jc/jc_6_add_9'; 
		// 			$data['sales'] 		 = $this->mglobal->get_sales($cluster_id, array(9) );
		// 		}
			$data['title']     = 'Penambahan Journey Cycle :: Merchandiser';
		}
		else {
			$data['title']     = 'Journey Cycle :: Merchandiser';
			$data['main_view'] = 'jc/jc_6';
		}

		$data['username']  = $this->mglobal->get_sales($cluster_id, array(4));
		
		$teritori = explode('>', $this->mglobal->getTerritoryPath());
		$data['region'] = $teritori[0];
		$data['cluster'] = $teritori[1];
		$spv = $this->session->userdata('username');
		if ($teritori[1]) {
			$data['journey'] = $this->mjc->merchan_jc( $spv);
			// print_r ($data['journey']);
			$this->load->view($template, $data, FALSE);
		}
		else
		{
			redirect("index.php/cluster");
		}


	}

	public function journey_merchan() {
		// $uri      = $this->input->post('uri');
		$sales_id = explode(';',$this->input->post("sales_id"));
		
		$date     = explode('-',$this->input->post('date'));
		$spv      = explode(';',$this->input->post('spv_id'));
		$keyword  = strtolower($this->input->post('keyword'));
		$jc_type  = $this->input->post('jc_type');
		$as_type  = $this->input->post('mobsel');
		// $limit   = "";  $offset;

		// $keyword    = strtoupper($this->input->post("keyword")); // Search parameters
		$month      = $this->input->post("month_work"); // yyyymm
		$agent_type = $this->input->post("agent_type"); // for Direct Sales (Agent) Only

		## secondary parameters
		// $keyword      = strtoupper($this->input->post("keyword")); // Search parameters
		$load_more    = $this->input->post("_s1"); // define if the "load more data" is requested
		if ($load_more) {
			$limit      = 10;
			$offset     = $this->input->post("_s2");
		}
		else {
			$limit = 15; $offset = 0; 
		}

		$journey = $this->mjc->get_merchan_jc($sales_id, $date, $spv, $keyword, $jc_type, $as_type, $limit, $offset);
		if($load_more)
			{ $journey_table = $this->load->view('jc/jc_append_'.$sales_id[1], array('journey' =>$journey, 'date'=> $date, 'offset' => $offset), TRUE); }
		else 
			{ $journey_table = $this->load->view('jc/jc_table_'.$sales_id[1], array('journey' =>$journey, 'date'=> $date), TRUE); }

		echo json_encode(
				array(
						'journey_summary' => $journey_table,
						'journey_count'   => count($journey) 
					)
			);

	}

// http://localhost/sts/index.php/jc/addOutletToJC

	public function addOutletToJC()
	{
		$param    = explode('_', $this->input->post("userId") );
		$yyyymm   = $this->input->post('yyyymm');
		$channels = $this->input->post("outletId");

		$data = array();
		for ($i=0; $i < count($channels); $i++) { 
			$data[$i]["user_id"]      = $param[1];
			$data[$i]["channel_id"]   = $channels[$i];
			$data[$i]["territory_id"] = $this->mglobal->f_get_territory_id_by_channel_id($channels[$i]);
			$data[$i]["yyyymm"]       = $yyyymm;
			$data[$i]["d".date('j')]  = 1;
			$data[$i]["created_on"]   = date('Y-m-d H:i:s');
			$data[$i]["created_by"]   = $this->session->userdata('username') ;
		}
		
		$status = $this->mjc->createBlankJc($param[0], $data);
		
		return $status;
	}

	/**
	 * Datatables
	 */
	public function dataList($param = null, $month, $program_id = null)
	{
		$user = explode('_', $param);

// SELECT 
// FROM t_mtr_outlet oo join t_mtr_territory tt on tt.territory_id = oo.territory_id 
// join t_mtr_channel_program cp on cp.territory_id = oo.territory_id 
// join t_mtr_program pp on pp.program_id = cp.program_id
// left JOIN t_jc_36 jc on jc.channel_id != oo.outlet_id 
// WHERE pp.program_type_id = 5 and tt.parent_id = 72 and cp.program_id != 924

    $cluster_id   = $this->mglobal->user_cluster($this->session->userdata('username'));
    $aColumns     = array('oo.outlet_id', 'oo.outlet_name', 'oo.address', 'pp.caption');
    // $sIndexColumn = "ss.scan_in_id";
    $sTable       = "t_mtr_outlet oo join t_mtr_territory tt on tt.territory_id = oo.territory_id 
                     join t_mtr_channel_program cp on cp.territory_id = oo.territory_id 
                     join t_mtr_program pp on pp.program_id = cp.program_id ";

    $sLimit = "";
    if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
	    { $sLimit = "LIMIT ".intval( $_GET['iDisplayLength'] )." OFFSET ". intval( $_GET['iDisplayStart'] ); }
     
    if ( isset( $_GET['iSortCol_0'] ) )
    {
        $sOrder = "ORDER BY ";
        for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ )
        {
          if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
          { $sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]." ".($_GET['sSortDir_'.$i]==='desc' ? 'asc' : 'desc').", "; }
        }
         
        $sOrder = substr_replace( $sOrder, "", -2 );
        if ( $sOrder == "ORDER BY" )
	        { $sOrder = ""; }
    }
     
    $sWhere = "WHERE pp.program_type_id = 5 and pp.program_id = $program_id AND tt.parent_id = $cluster_id AND oo.territory_id not in (SELECT jc.territory_id from t_jc_".$user[0]." jc where jc.yyyymm = '$month' ) ";
    if ( $_GET['sSearch'] != "" )
    {
        $sWhere = "WHERE pp.program_type_id = 5 and pp.program_id = $program_id AND tt.parent_id = $cluster_id AND oo.territory_id not in (SELECT jc.territory_id from t_jc_".$user[0]." jc where jc.yyyymm = '$month' ) AND (";
        // $sWhere = "WHERE f_get_cluster(ss.user_id) = $cluster_id AND (";
        for ( $i=0 ; $i<count($aColumns) ; $i++ )
        {
          if ( $_GET['bSearchable_'.$i] == "true" )
          { $sWhere .= $aColumns[$i]." ILIKE '%".$this->db->escape_str( $_GET['sSearch'] )."%' OR "; }
        }
        $sWhere = substr_replace( $sWhere, "", -3 );
        $sWhere .= ")";
    }
     
    /* Individual column filtering */
    for ( $i=0 ; $i<count($aColumns) ; $i++ )
    {
      if ( $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
      {
        if ( $sWhere == "" )
          { $sWhere = "WHERE ll.lookup_key = 'outlet_category_ho' and tt.parent_id = $cluster_id "; }
        else
          { $sWhere .= " AND "; }
        $sWhere .= $aColumns[$i]." ILIKE '%".$this->db->escape_str($_GET['sSearch_'.$i])."%' ";
      }
    }
     
     
    $sQuery = "
        SELECT ".str_replace(" , ", " ", implode(", ", $aColumns))."
        FROM   $sTable
        $sWhere
        $sOrder
        $sLimit
    ";
    $rResult = $this->db->query($sQuery);
     
    $sQuery = "
			SELECT count(*) AS numrows
      FROM   $sTable
			WHERE pp.program_type_id = 5 and pp.program_id = $program_id AND tt.parent_id = $cluster_id AND oo.territory_id not in (SELECT jc.territory_id from t_jc_".$user[0]." jc where jc.yyyymm = '$month')
    ";
    $rResultTotal = $this->db->query($sQuery)->row(); 
    $iTotal       = $rResultTotal->numrows;
     
    if ( $sWhere != "" )
    {
      $sQuery = "
				SELECT count(*) AS numrows
        FROM   $sTable
        $sWhere
      ";
      $rResultFilterTotal = $this->db->query($sQuery)->row(); 
      $iFilteredTotal     = $rResultTotal->numrows;
    }
    else
    { $iFilteredTotal = $iTotal; }
     
    $output = array(
        "sEcho" => intval($_GET['sEcho']),
        "iTotalRecords" => $iTotal,
        "iTotalDisplayRecords" => $iFilteredTotal,
        "aaData" => array()
    );

    foreach ($rResult->result_array() as $aRow  )
    {
      $row = array();
      for ( $i=0 ; $i<count($aColumns) ; $i++ )
      {
        if ( $aColumns[$i] != null )
        {
        
          $colName = explode('.', $aColumns[$i]);
          $colName = $colName[1];
          
          $row[] = $aRow[ $colName ];
        }
      }
      $row[] ='<input type="checkbox" name="ck[]" value="'.$row[0].'" >';
      $output['aaData'][] = $row;
    }

    echo json_encode( $output );

  }

}
/* End of file jc.php */
/* Location: ./application/controller/jc.php */